IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spFiles_RetrieveAll')
	BEGIN
		PRINT 'Dropping Procedure spFiles_RetrieveAll'
		DROP  Procedure  spFiles_RetrieveAll
	END

GO

PRINT 'Creating Procedure spFiles_RetrieveAll'
GO

CREATE Procedure spFiles_RetrieveAll
	(
		@FileNo varchar(50) = null, 
		@BoxNo varchar(50) = null, 
		@DivisionName varchar(50) = null, 
		@DepartmentName varchar(50) = null, 
		@CategoryName varchar(50) = null, 
		@ProductName varchar(50) = null, 
		@Archive_ID int = null, 
		@FileStatus varchar(50) = null, 
		@Notes varchar(250) = null, 
		@Description varchar(100) = null, 
		@FromNo int = null, 
		@ToNo int = null, 
		@FromDate DateTime = null, 
		@ToDate DateTime = null, 
		@FromAlpha varchar(50) = null, 
		@ToAlpha varchar(50) = null, 
		@CreatedDate DateTime = null, 
		@CreatedByUser_ID numeric(9,0) = null, 
		@Request_ID int = null, 
		@RequestDate DateTime = null, 
		@RequestedByUser_ID numeric(9,0) = null, 
		@RequestedDate DateTime = null
	)
AS
    /* 
    ------------------------------------------------------------------------------
    - Change History
    - --------------
    - Date          Description
    ------------------------------------------------------------------------------
    - 2007/11/02	Initial version created
    ------------------------------------------------------------------------------
    */
	
	SET NOCOUNT ON
	
	-- Retrieve the relevant records
	SELECT  *
	FROM	Files
	
	WHERE	(@FileNo IS NULL OR Files.FileNo LIKE @FileNo + '%')
	  AND	(@BoxNo IS NULL OR Files.BoxNo LIKE @BoxNo + '%')
	  AND	(@DivisionName IS NULL OR Files.DivisionName LIKE @DivisionName + '%')
	  AND	(@DepartmentName IS NULL OR Files.DepartmentName LIKE @DepartmentName + '%')
	  AND	(@CategoryName IS NULL OR Files.CategoryName LIKE @CategoryName + '%')
	  AND	(@ProductName IS NULL OR Files.ProductName LIKE @ProductName + '%')
	  AND	(@Archive_ID IS NULL OR Files.Archive_ID = @Archive_ID)
	  AND	(@FileStatus IS NULL OR Files.FileStatus LIKE @FileStatus + '%')
	  AND	(@Notes IS NULL OR Files.Notes LIKE @Notes + '%')
	  AND	(@Description IS NULL OR Files.Description LIKE @Description + '%')
	  AND	(@FromNo IS NULL OR Files.FromNo = @FromNo)
	  AND	(@ToNo IS NULL OR Files.ToNo = @ToNo)
	  AND	(@FromDate IS NULL OR Files.FromDate = @FromDate)
	  AND	(@ToDate IS NULL OR Files.ToDate = @ToDate)
	  AND	(@FromAlpha IS NULL OR Files.FromAlpha LIKE @FromAlpha + '%')
	  AND	(@ToAlpha IS NULL OR Files.ToAlpha LIKE @ToAlpha + '%')
	  AND	(@CreatedDate IS NULL OR Files.CreatedDate = @CreatedDate)
	  AND	(@CreatedByUser_ID IS NULL OR Files.CreatedByUser_ID = @CreatedByUser_ID)
	  AND	(@Request_ID IS NULL OR Files.Request_ID = @Request_ID)
	  AND	(@RequestDate IS NULL OR Files.RequestDate = @RequestDate)
	  AND	(@RequestedByUser_ID IS NULL OR Files.RequestedByUser_ID = @RequestedByUser_ID)
	  AND	(@RequestedDate IS NULL OR Files.RequestedDate = @RequestedDate)

	-- Return to calling method
	RETURN
GO
